﻿Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Globalization
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Text
Imports System.IO

Partial Public Class MyclsDBUtility

    '廣告管理

#Region "列表"

    '列表-Select
    Public Function Banner_Select() As DataTable

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        sql.Length = 0

        sql.AppendFormat("SELECT * FROM [Banner]")

        Dim DS As DataSet = dat.GetData(sql.ToString, Nothing, Data.CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then

            Return DS.Tables(0)

        Else

            Return Nothing

        End If

    End Function

    '列表-Select要刪除的檔案資訊
    Public Function Banner_SelectDelFileInfo(ByVal original_BannerID As Integer) As DataTable

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Dim sqlParams As SqlParameter() = New SqlParameter(0) {}

        sqlParams(0) = New SqlParameter("@original_BannerID", SqlDbType.Int)
        sqlParams(0).Value = original_BannerID

        sql.Length = 0

        sql.AppendFormat("SELECT * FROM [FD_UploadFiles] A inner join [Banner] B on A.FileSaveName=B.FileID WHERE B.BannerID=@original_BannerID;")

        Dim DS As DataSet = dat.GetData(sql.ToString, sqlParams, Data.CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then

            Return DS.Tables(0)

        Else

            Return Nothing

        End If

    End Function

    '列表-Delete
    Public Sub Banner_Delete(ByVal original_BannerID As Integer)

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Dim sqlParams As SqlParameter() = New SqlParameter(0) {}

        sqlParams(0) = New SqlParameter("@original_BannerID", SqlDbType.Int)
        sqlParams(0).Value = original_BannerID

        sql.Length = 0

        Using scope As New Transactions.TransactionScope

            Dim DT As DataTable = Banner_SelectDelFileInfo(original_BannerID)

            sql.AppendFormat("DELETE A FROM [FD_UploadFiles] A inner join [Banner] B on A.FileSaveName=B.FileID WHERE B.BannerID=@original_BannerID; ")
            sql.AppendFormat("DELETE [Banner] WHERE BannerID=@original_BannerID; ")

            dat.AccData(sql.ToString, sqlParams, Data.CommandType.Text)

            scope.Complete()

            If DT IsNot Nothing And DT.Rows.Count > 0 Then

                Dim theFile As New FileInfo(HttpContext.Current.Server.MapPath(DT.Rows(0)("FileSavePath").ToString()) + DT.Rows(0)("FileSaveName").ToString())

                If theFile.Exists Then
                    theFile.Delete()
                End If

            End If

        End Using

    End Sub

#End Region

#Region "編輯"
    '編輯-Select
    Public Function Banner_SelectByBannerID(ByVal BannerID As Integer) As DataTable

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Dim sqlParams As SqlParameter() = New SqlParameter(0) {}

        sqlParams(0) = New SqlParameter("@BannerID", SqlDbType.Int)
        sqlParams(0).Value = BannerID

        sql.Length = 0

        'sql.AppendFormat("SELECT A.*,(B.FileSavePath+CAST(B.FileSaveName as varchar(100))) as theFileName,B.FileSaveName ")
        sql.AppendFormat("SELECT A.*,(B.FileSavePath+CAST(B.FileSaveName as varchar(100)))+'.'+FileType as theFileName,B.FileSaveName ")
        sql.AppendFormat("FROM [Banner] A ")
        sql.AppendFormat("left join[FD_UploadFiles]B on A.FileID=B.FileSaveName ")
        sql.AppendFormat("WHERE A.BannerID=@BannerID ")

        Dim DS As DataSet = dat.GetData(sql.ToString, sqlParams, Data.CommandType.Text, "dt")

        If DS IsNot Nothing AndAlso DS.Tables.Count > 0 Then

            Return DS.Tables(0)

        Else

            Return Nothing

        End If

    End Function

    '編輯-Insert
    Public Sub Banner_Insert(ByVal theClass As String, ByVal theType As String, ByVal FileName As String, ByVal FileShortName As String, ByVal FileType As String, ByVal FileSavePath As String, _
        ByVal FileSaveName As System.Guid, ByVal FileDesc As String, ByVal CreateBy As String, ByVal Url As String, ByVal Active As String)

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Dim sqlParams As SqlParameter() = New SqlParameter(10) {}

        sqlParams(0) = New SqlParameter("@theClass", SqlDbType.VarChar, 20)
        sqlParams(0).Value = theClass
        sqlParams(1) = New SqlParameter("@theType", SqlDbType.VarChar, 20)
        sqlParams(1).Value = theType
        sqlParams(2) = New SqlParameter("@FileName", SqlDbType.NVarChar, 200)
        sqlParams(2).Value = FileName
        sqlParams(3) = New SqlParameter("@FileShortName", SqlDbType.NVarChar, 200)
        sqlParams(3).Value = FileShortName
        sqlParams(4) = New SqlParameter("@FileType", SqlDbType.VarChar, 50)
        sqlParams(4).Value = FileType
        sqlParams(5) = New SqlParameter("@FileSavePath", SqlDbType.NVarChar, 1000)
        sqlParams(5).Value = FileSavePath
        sqlParams(6) = New SqlParameter("@FileSaveName", SqlDbType.UniqueIdentifier)
        sqlParams(6).Value = FileSaveName
        sqlParams(7) = New SqlParameter("@FileDesc", SqlDbType.NVarChar, 200)
        sqlParams(7).Value = FileDesc
        sqlParams(8) = New SqlParameter("@CreateBy", SqlDbType.NVarChar, 50)
        sqlParams(8).Value = CreateBy

        sqlParams(9) = New SqlParameter("@Url", SqlDbType.NVarChar, 50)
        sqlParams(9).Value = Url
        sqlParams(10) = New SqlParameter("@Active", SqlDbType.NChar, 1)
        sqlParams(10).Value = Active

        sql.Length = 0

        Using scope As New Transactions.TransactionScope

            '新增一筆到UploadFiles
            sql.AppendFormat("INSERT FD_UploadFiles(theClass,theType,FileName,FileShortName,FileType,FileSavePath,FileSaveName,FileDesc,CreateBy,CreateAt) ")
            sql.AppendFormat("SELECT @theClass,@theType,@FileName,@FileShortName,@FileType,@FileSavePath,@FileSaveName,@FileDesc,@CreateBy,getdate(); ")

            '新增一筆到Banner
            sql.AppendFormat("INSERT [Banner] (Description,FileID,Url,Active) SELECT @FileDesc,@FileSaveName,@Url,@Active; ")

            dat.AccData(sql.ToString, sqlParams, Data.CommandType.Text)

            scope.Complete()

        End Using

    End Sub

    '編輯-Update
    Public Sub Banner_Update(ByVal FileName As String, ByVal FileShortName As String, ByVal FileType As String, ByVal FileSavePath As String, _
        ByVal FileSaveName As System.Guid, ByVal FileDesc As String, ByVal CreateBy As String, ByVal Url As String, ByVal Active As String, ByVal original_BannerID As Integer)

        Dim dat As New clsData
        Dim sql As New MyStringBuilder

        Dim sqlParams As SqlParameter() = New SqlParameter(9) {}

        sqlParams(0) = New SqlParameter("@FileName", SqlDbType.NVarChar, 200)
        sqlParams(0).Value = FileName
        sqlParams(1) = New SqlParameter("@FileShortName", SqlDbType.NVarChar, 200)
        sqlParams(1).Value = FileShortName
        sqlParams(2) = New SqlParameter("@FileType", SqlDbType.VarChar, 50)
        sqlParams(2).Value = FileType
        sqlParams(3) = New SqlParameter("@FileSavePath", SqlDbType.NVarChar, 1000)
        sqlParams(3).Value = FileSavePath
        sqlParams(4) = New SqlParameter("@FileSaveName", SqlDbType.UniqueIdentifier)
        sqlParams(4).Value = FileSaveName
        sqlParams(5) = New SqlParameter("@FileDesc", SqlDbType.NVarChar, 200)
        sqlParams(5).Value = FileDesc
        sqlParams(6) = New SqlParameter("@CreateBy", SqlDbType.NVarChar, 50)
        sqlParams(6).Value = CreateBy

        sqlParams(7) = New SqlParameter("@Url", SqlDbType.NVarChar, 50)
        sqlParams(7).Value = Url
        sqlParams(8) = New SqlParameter("@Active", SqlDbType.NChar, 1)
        sqlParams(8).Value = Active

        sqlParams(9) = New SqlParameter("@original_BannerID", SqlDbType.Int)
        sqlParams(9).Value = original_BannerID

        sql.Length = 0

        Using scope As New Transactions.TransactionScope

            If FileName <> Nothing Then
                '更新UploadFiles
                sql.AppendFormat("UPDATE A SET A.FileName=@FileName,A.FileShortName=@FileShortName,A.FileType=@FileType,A.FileSavePath=@FileSavePath,A.FileSaveName=@FileSaveName,A.FileDesc=@FileDesc,A.CreateBy=@CreateBy,A.CreateAt=getdate() ")
                sql.AppendFormat("FROM [FD_UploadFiles] A inner join [Banner] B on A.FileSaveName=B.FileID WHERE B.BannerID=@original_BannerID; ")

                '更新Banner
                sql.AppendFormat("UPDATE [Banner] SET Description=@FileDesc,FileID=@FileSaveName,Url=@Url,Active=@Active WHERE BannerID=@original_BannerID; ")
            Else
                '更新Banner
                sql.AppendFormat("UPDATE [Banner] SET Description=@FileDesc,Url=@Url,Active=@Active WHERE BannerID=@original_BannerID; ")

            End If


            dat.AccData(sql.ToString, sqlParams, Data.CommandType.Text)

            scope.Complete()

        End Using

    End Sub

#End Region







End Class
